var db = window.sqlitePlugin.openDatabase({
    name: 'my.db',
    location: 'default',
    androidDatabaseProvider: 'system'
});

export function init() {
    db.transaction(function (tx) {
        tx.executeSql('CREATE TABLE IF NOT EXISTS code_table (code text, number integer,timestamp BIGINT)');
        //删除前一天数据
        var now = new Date();
        now.setHours(0, 0, 0, 0)
        var l = now.getTime()
        tx.executeSql('DELETE FROM code_table WHERE timestamp < ?', [l]);
    });
}

export function initDeviceSetting() {
    db.transaction(function(tx) {
        tx.executeSql('CREATE TABLE IF NOT EXISTS device_setting (id INTEGER PRIMARY KEY AUTOINCREMENT, device_name TEXT NOT NULL, packing_limit INTEGER)');
    }, function(error) {
        console.log('Transaction ERROR: ' + error.message);
    }, function() {
        console.log('Table created or already exists');
        
        // 检查表中是否有数据
        db.transaction(function(tx) {
            tx.executeSql('SELECT COUNT(*) AS count FROM device_setting', [], function(tx, result) {
                var count = result.rows.item(0).count;
                if (count === 0) {
                    // 表为空，插入数据
                    var initialData = [
                        ['扶手', 15],
                        ['FIN-A', 27],
                        ['IP', 32],
                        ['FIN-B', 60],
                        ['中控', 10]
                    ];

                    initialData.forEach(function(item) {
                        tx.executeSql('INSERT INTO device_setting (device_name, packing_limit) VALUES (?, ?)', item, function(tx, res) {
                            console.log("Row inserted: " + res.insertId);
                        }, function(error) {
                            console.log("Error: " + error.message);
                        });
                    });
                } else {
                    console.log('Data already exists, no need to initialize.');
                }
            }, function(error) {
                console.log('Error checking data count: ' + error.message);
            });
        });
    });
}

export function updateDeviceSetting(id, packing_limit) {
    return new Promise((resolve, reject) => {
        db.transaction(function (tx) {
            tx.executeSql('UPDATE device_setting SET packing_limit = ? WHERE id = ?', [packing_limit, id], function (tx, res) {
                resolve(res);
            }, function (tx, error) {
                reject('Transaction ERROR: ' + error.message);
            });
        });
    });
}


export function getDeviceSettings() {
    return new Promise((resolve, reject) => {
        db.transaction(function (tx) {
            tx.executeSql('SELECT * FROM device_setting', [], function (tx, res) {
                if (res.rows.length > 0) {
                    const settings = [];
                    for (let i = 0; i < res.rows.length; i++) {
                        settings.push(res.rows.item(i));
                    }
                    resolve(settings);
                } else {
                    resolve(null);
                }
            });
        }, (e) => {
            reject(e)
        });
    });
}
export function getDeviceSettingByDeviceName(device_name) {
    return new Promise((resolve, reject) => {
        db.transaction(function (tx) {
            tx.executeSql('SELECT packing_limit FROM device_setting WHERE device_name = ?', [device_name], function (tx, res) {
                if (res.rows.length > 0) {
                    resolve(res.rows.item(0));
                } else {
                    resolve(null);
                }
            });
        }, (e) => {
            reject(e)
        });
    });
}   


export function saveCode(code) {
    db.transaction(function (tx) {
        //先删除 再添加
        tx.executeSql('DELETE FROM code_table WHERE code = ?', [code.code]);
        tx.executeSql('INSERT INTO code_table (code, number, timestamp) VALUES (?,?,?)', [code.code, code.number, new Date().getTime()]);
    }, function (error) {
        console.log('Transaction ERROR: ' + error.message);
    }, function () {
        console.log('Populated database OK');
    });

}
export function getCode(id) {
    return new Promise((resolve, reject) => {
        db.transaction(function (tx) {
            tx.executeSql('SELECT * FROM code_table WHERE code = ?', [id], function (tx, res) {
                if (res.rows.length > 0) {
                    resolve(res.rows.item(0));
                } else {
                    resolve(null);
                }
            });
        }, (e) => {
            reject(e)
        });
    });
}